package com.sreManager.controller.web;

import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.json.JSONException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import com.sreManager.Constant;
import com.sreManager.service.UploadService;

@Controller
@RequestMapping(value={"/upload/"})
public class UploadController extends CommonController{
	@Autowired UploadService uploadService;

	String TILESForder = "upload";
	@ModelAttribute("menuType")
	public String type(){
		return TILESForder;
	}
	/**
	 * 사업자정보 업로드 페이지
	 * @return
	 */
	@RequestMapping(value={"form"})
	public String form(){
		return TILESForder+"/form"+Constant.TILES;
	}

	/**
	 * 측정정보 업로드
	 * @return
	 */
	@RequestMapping(value="insertEstimate.json")
	public @ResponseBody Map<String, Object> insertEstimate(HttpServletRequest request, HttpServletResponse response){
		Map<String, Object> result = new HashMap<String, Object>();
		//JSON DATA FORMAT
		//json:{root:[{...},{...}..]}
		try{
			org.json.JSONObject jsonData = new org.json.JSONObject(request.getParameter("json").toString());
			org.json.JSONArray jsonArray = jsonData.getJSONArray("root");

			List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
			Map<String, Object> rowHashMap = null;

			for (int i = 0; i < jsonArray.length(); i++) {
				rowHashMap = new HashMap<String, Object>();
				org.json.JSONObject jsonObj = jsonArray.getJSONObject(i);
				Iterator<?> iterator = jsonObj.keys();
				while(iterator.hasNext()){
					String key = iterator.next().toString();
					rowHashMap.put(key, jsonObj.get(key));
				}
				list.add(i, rowHashMap);
			}

			list = uploadService.insertEstimates(list);
			result.put("excel", list);

    		// 로그
    		logsService.insertLog(request, Constant.LOG_TYPE_UPLOAD, Constant.MENU_UPLOAD);

		} catch(Exception e){
			e.printStackTrace();
		}
		return result;
	}


	//파일정보추출(공통)
	@RequestMapping(value="fileInfo")
	public void getFileDownload(HttpServletRequest request,
			HttpServletResponse response) throws IOException {
		System.out.println("here");
		// TODO Auto-generated method stub
		MultipartHttpServletRequest mhsr = (MultipartHttpServletRequest) request;
		Map<String, MultipartFile> files = mhsr.getFileMap();

		Set<String> set = files.keySet();
		Iterator<String> iterator =  set.iterator();
		MultipartFile file = null;

		List<Object> list = new ArrayList<Object>();
		List<Object> excelList = new ArrayList<Object>();

		while(iterator.hasNext()) {
			Map<String, Object> map = new HashMap<String, Object>();
			String key = iterator.next();
			file = files.get(key);
			List<Object> exclList = null;
			if(file == null || file.getSize() == 0){
				exclList = new ArrayList<Object>();
				map.put("sizeB", 0);
				map.put("sizeKB", 0);
				map.put("sizeMB", 0);
				map.put("fieldName", null);
				map.put("fileName", null);
			}
			else if (file.getOriginalFilename().length() < 5
					|| !".xls".equals(
							file.getOriginalFilename().substring(
								file.getOriginalFilename().length() - 4,
								file.getOriginalFilename().length()))) {
				exclList = new ArrayList<Object>();
				map.put("sizeB", file.getSize());
				map.put("sizeKB", (double) file.getSize() / 1024);
				map.put("sizeMB", (double) file.getSize() / 1048576);
				map.put("fieldName", file.getName());
				map.put("fileName", file.getOriginalFilename());
			}
			else{
				exclList = getExcelByJson(request, file);
				map.put("sizeB", file.getSize());
				map.put("sizeKB", (double) file.getSize() / 1024);
				map.put("sizeMB", (double) file.getSize() / 1048576);
				map.put("fieldName", file.getName());
				map.put("fileName", file.getOriginalFilename());
			}


			list.add(map);

			excelList.add(exclList);
		}

		org.json.JSONObject json = new org.json.JSONObject();
		try {
			json.put("file", list);
			json.put("excel", excelList);
		} catch (JSONException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		response.setContentType("text/html");
		response.setCharacterEncoding("UTF-8");
		response.getWriter().write(json.toString());
	}

	//엑셀 추출함수(공통)
	@SuppressWarnings("unchecked")
	private List<Object> getExcelByJson(HttpServletRequest request, MultipartFile file) throws IOException {
		List<String> fields = (List<String>) request.getAttribute("fields");

		POIFSFileSystem fileSystem = new POIFSFileSystem(file.getInputStream());
		HSSFWorkbook workbook = new HSSFWorkbook(fileSystem);

		HSSFSheet sheet = workbook.getSheetAt(0); // 첫번째 쉬트
		List<Object> list = new ArrayList<Object>();
		Hashtable<String, String> ht = null;

		int rows = sheet.getPhysicalNumberOfRows(); // 행개수
		for (int i = 1; i < rows; i++) {
			ht = new Hashtable<String, String>();

			HSSFRow row = sheet.getRow(i); // row 가져오기
			if (row != null) {
				int cells = row.getLastCellNum(); // 셀 개수 가져오기
					for (short c = 0; c < cells; c++) {
						HSSFCell cell = row.getCell(c);
						if (cell != null) {
							String value = null;
							switch (cell.getCellType()) { // 각셀의 데이터값을 가져올때 맞는데이터형으로 변환한다.
								case HSSFCell.CELL_TYPE_FORMULA:
									value = cell.getCellFormula();
									break;
								case HSSFCell.CELL_TYPE_NUMERIC:
									if (HSSFDateUtil.isCellDateFormatted(cell)){
								        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
								        value = formatter.format(cell.getDateCellValue());
								    } else {
								        value = "" + (long)cell.getNumericCellValue();
								    }									break;
								case HSSFCell.CELL_TYPE_STRING:
									value = "" + cell.getStringCellValue();
									break;
								case HSSFCell.CELL_TYPE_BLANK:
									value = "";
									break;
								case HSSFCell.CELL_TYPE_ERROR:
									value = "" + cell.getErrorCellValue();
									break;
								default:
									value = "";
							}
							// hashtable 에값을 넣어 둔다.
							String checkStr = value.replaceAll(" ", "");

							if("".equals(checkStr)) {
								value = "";
							}
							if(fields != null) {
								ht.put(fields.get(cell.getCellNum()), value.replaceAll("\n", " <br/>"));
							}
							else {
								ht.put("excel_" + cell.getCellNum(), value.replaceAll("\n", " <br/>"));
							}
						} else {
							int cellNum = 99;
							String cellName = "default";
							if(cell != null) {
								cellNum = cell.getCellNum();
								if(fields != null) {
									cellName = fields.get(cellNum);
								}
							}
							if(fields != null) {
								ht.put(cellName, "");
							}
							else {
								ht.put("excel_" + cellNum, "");
							}

						}
					}
			}
			//추출한 row 중 유효한 데이터가 2개 이상일때만 출력
			if(ht.size() > 1){
				Iterator<String> iterator = ht.keySet().iterator();
				boolean flag = false;
				int notEmptyCount = 0;
				while(iterator.hasNext()) {
					String key = iterator.next();
					if(!"".equals(ht.get(key))) {
						notEmptyCount++;
						if(notEmptyCount > 1){
							flag = true;
							break;
						}
					}
				}
				if(flag) {
					list.add(ht);
				}
			}
		}
		return list;
	}
}
